# coding=utf-8

from framework.db.mysql import BaseDb
from tornado import gen
from lib import log
import time

class Device(BaseDb):

    @gen.coroutine
    def select_device(self, Fschool_id, page, offset):
        query = '''SELECT Fid, Fname, Fdorm_id, Froom_id, Fimg 
                FROM tb_material 
                WHERE Fschool_id = %s 
                LIMIT %s, %s'''
        count_sql = 'SELECT count(Fid) FROM tb_material WHERE Fschool_id = %s'

        ret = yield self.executejson(query, (Fschool_id, page, offset))
        count = yield self.execute(count_sql, (Fschool_id))
        raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def save_device_log(self, params):
        count = yield self.insert("tb_repair_log", params)
        self.commit()
        raise gen.Return(count)

    @gen.coroutine
    def find_room(self, Fschool_id, dorm):
        query = 'SELECT Fid, Fname FROM tb_room WHERE Fdorm_id = %s AND Fschool_id = %s'
        ret = yield self.executejson(query, (dorm, Fschool_id))
        raise gen.Return(ret)

    @gen.coroutine
    def find_device(self, Fschool_id, dorm, room):
        query = 'SELECT Fid, Fname FROM tb_material WHERE Froom_id = %s AND Fdorm_id = %s AND Fschool_id = %s'
        ret = yield self.executejson(query, (room, dorm, Fschool_id))
        raise gen.Return(ret)

    @gen.coroutine
    def get_info(self, Fschool_id, Did):
        query = '''SELECT a.Fid, a.Fname, a.Ftype_id, a.Fdorm_id, a.Froom_id
                , b.Fname Ftype_name, c.Fdorm_name, d.Fname Froom_name
                FROM tb_material a 
                LEFT JOIN tb_material_type b 
                ON a.Ftype_id = b.Fid
                LEFT JOIN tb_dorm c 
                ON a.Fdorm_id = c.Fid
                LEFT JOIN tb_room d 
                ON a.Froom_id = d.Fid
                WHERE a.Fschool_id = %s 
                AND a.Fid = %s
                LIMIT 1'''
        ret = yield self.executejson(query, (Fschool_id, Did))
        raise gen.Return(ret[0])

    @gen.coroutine
    def select_old_device(self, Fschool_id, Fstudent_id, page, offset, s):
        query = '''SELECT Fid, Fimg, Fdevice_name, Fremark, Fthumb, Fstatus 
                , Frepair_name, Fcreate_time
                FROM tb_repair_log 
                WHERE Fschool_id = %s 
                AND Fuser_id = %s %s
                ORDER BY Fcreate_time DESC
                LIMIT %s, %s'''
        count_sql = 'SELECT count(Fid) FROM tb_repair_log WHERE Fschool_id = %s AND Fuser_id = %s %s'

        stmp = ''
        if s == '1':
            stmp = 'AND Fstatus = "3"'
        elif s == '2':
            stmp = 'AND Fstatus = "1"'

        query = query% ('%s', '%s', stmp, '%s', '%s')
        ret = yield self.executejson(query, (Fschool_id, Fstudent_id, page, offset))
        count_sql = count_sql% ('%s', '%s', stmp)
        count = yield self.execute(count_sql, (Fschool_id, Fstudent_id))
        raise gen.Return((ret, count[0][0]))

    @gen.coroutine
    def set_status(self, school_id, Fstudent_id, Fid):
        update = 'UPDATE tb_repair_log SET Fthumb = Fthumb*-1 WHERE Fschool_id = %s AND Fuser_id = %s AND Fid = %s'

        print update% (school_id, Fstudent_id, Fid)
        yield self.upsert_commit(update, (school_id, Fstudent_id, Fid))
        raise gen.Return()